﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;

namespace BLL
{
   public class ClsHamLuongViSinhBus
    {
       public ClsHamLuongViSinhInfo Info = new ClsHamLuongViSinhInfo();
       public DataTable SelectOne()
       {
           ClsDataAccess oConnection = new ClsDataAccess();
          string strSql = @" SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,HamLuongViSinh.MaThiNghiem,HamLuongViSinh.TongColiform,HamLuongViSinh.EColiform,HamLuongViSinh.TongViTrung,HamLuongViSinh.ViTrungHieuKhi,HamLuongViSinh.TrucTrungDaiTrang,HamLuongViSinh.KetLuanSuDung
                              FROM TTCHUNG,DuLieuMau,HamLuongViSinh
                              WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                              AND DuLieuMau.MaMau=HamLuongViSinh.MaMau 
                              AND TTCHUNG.MaGK like'%" + Info.MaGK +
                              "%'AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";

           if (oConnection.ExecuteSelect(strSql))
           {
               oConnection.ODataTable.Columns.Add("STT");
               int i = 0;
               foreach (DataRow oDRow in oConnection.ODataTable.Rows)
               {
                   oDRow["STT"] = (i + 1);
                   i++;
               }
               return oConnection.ODataTable;
           }
           else return null;
       }
        public DataTable SelectAll()
        {//,,
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @" SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,HamLuongViSinh.MaThiNghiem,HamLuongViSinh.TongColiform,HamLuongViSinh.EColiform,HamLuongViSinh.TongViTrung,HamLuongViSinh.ViTrungHieuKhi,HamLuongViSinh.TrucTrungDaiTrang,HamLuongViSinh.KetLuanSuDung
                              FROM TTCHUNG,DuLieuMau,HamLuongViSinh
                              WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                              AND DuLieuMau.MaMau=HamLuongViSinh.MaMau
                              AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";
            //string strSql = @"SELECT * FROM HamLuongViSinh";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectMau()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT MaMau,TenMau FROM DuLieuMau, TTCHUNG WHERE TTCHUNG.MaGK=DuLieuMau.MaGK AND TTCHUNG.Tinh LIKE '" + Info.MaTinh + "'";

            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectThiNghiem()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT MaThiNghiem FROM TTThiNghiemMau";

            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertHamLuongViSinh '" + Info.MaMau + "','" + Info.MaThiNghiem + "','" + Info.TongColiform + "','" + Info.EColiform + "','" + Info.TongViTrung + "','" + Info.ViTrungHieuKhi + "','" + Info.TrucTrungDaiTrang + "',N'" + Info.KetLuanSuDung + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateHamLuongViSinh '" + Info.MaMau +"','" + Info.MaThiNghiem + "','" + Info.TongColiform + "','" + Info.EColiform + "','" + Info.TongViTrung + "','" + Info.ViTrungHieuKhi + "','" + Info.TrucTrungDaiTrang + "',N'" + Info.KetLuanSuDung + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }

        public bool Xoa()
        {
            string strSql = "EXECUTE DeleteHamLuongViSinh'" + Info.MaMau + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }
    }
}
